﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DuSQL.API
{
    public class BLLDbHelper
    {
        public static readonly BLLDbHelper Instance = new BLLDbHelper();
        public DataTable GetShemaTable(string connectionStr, NodeType databaseType)
        {
            switch (databaseType)
            {
                case NodeType.DbConnection_Access:
                    {
                        return DbHelper.GetShemaTable(connectionStr, "Table");
                    }
                case NodeType.DbConnection_SqlServer:
                    {
                        return GetShemaTable_SqlServer(connectionStr);
                    }                  
            }
            return null;
        }

        public DataTable GetShemaView(string connectionStr, NodeType databaseType)
        {
            switch (databaseType)
            {
                case NodeType.DbConnection_Access:
                    {
                        return DbHelper.GetShemaTable(connectionStr, "View");
                    }
                case NodeType.DbConnection_SqlServer:
                    {
                        return GetShemaView_SqlServer(connectionStr);
                    }
            }
            return null;
        }
        public DataView GetShemaColumn(string conectionStr, string tablename,NodeType databaseType)
        {
            switch (databaseType)
            {
                case NodeType.DbConnection_Access:
                    {
                        DataView dv = DbHelper.GetShemaColumnName(conectionStr, tablename).DefaultView;
                        dv.Sort = "ordinal_position";
                        return dv;
                    }
                case NodeType.DbConnection_SqlServer:
                    {
                        DataView dv=GetTableColumn_SqlServer(conectionStr,tablename).DefaultView;

                        return dv;
                    }
            }
            return null;
        }

        public DataTable GetStroProcedure(string connectionStr,NodeType databaseType)
        {
            switch (databaseType)
            {
                case NodeType.DbConnection_SqlServer:
                    {
                        return GetStroProcedure_SqlServer(connectionStr);
                    }
            }
            return null;

        }
        

        #region sqlserver获取表,视图,存储过程
        private DataTable GetShemaTable_SqlServer(string  connectionStr)
        {
            string sql = " SELECT  name as table_name  FROM   sysobjects   WHERE   xtype   =   'U'   AND   name   <>   'dtproperties' order by name";
            return DbHelperSQL.Query(connectionStr, sql).Tables[0];           
        }
        private DataTable GetTableColumn_SqlServer(string connectionStr, string tablename)
        {
            string sql = "SELECT Name as Column_Name FROM SysColumns WHERE id=Object_Id('"+tablename+"')";
            return DbHelperSQL.Query(connectionStr, sql).Tables[0];
        }
        private DataTable GetShemaView_SqlServer(string connectionStr)
        {
            string sql = " select name as table_name  from sysobjects where type='V' order by name";
            return DbHelperSQL.Query(connectionStr, sql).Tables[0];      
        }
        private DataTable GetStroProcedure_SqlServer(string connectionStr)
        {
            string sql = "select  a.name as SpName from  sysobjects  a where  a.xtype='P'  and  a.status>=0 order  by  a.name ";
            return DbHelperSQL.Query(connectionStr, sql).Tables[0];
        }



      

        #endregion
    }
}
